using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;

using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Servico.Bovespa.BovespaBase.Util;
using Sinacor.Servicos.Comum.Util.SGBD;


namespace Sinacor.Servico.Bovespa.Ordens.ResourceAccess
{
    public class Bolsa : BaseResourceAccess
    {

        public Bolsa()
            : base(AuthorizationContext.Current.CompanyId) 
        {
        
        }
        /// <summary>
        /// 02/01/2008
        /// Luciano Azevedo
        /// Consulta de Bolsa por Codigo
        /// </summary>
        /// <param name="CodigoBolsa"></param>
        /// <returns></returns>
        public BusinessEntity.Bolsa ConsultarBolsa(string CodigoBolsa)
        {                        
            IDataReader reader;
            BusinessEntity.Bolsa response = new BusinessEntity.Bolsa();
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(" SELECT");
            sql.AppendLine(" NM_RESU_BOLSA,");
            sql.AppendLine(" CD_BOLSA");
            sql.AppendLine(" FROM TGEBOLSA");
            sql.AppendLine(" WHERE CD_SISTEMA = 'BOL'");
            sql.AppendLine(" AND CD_BOLSA = :CD_BOLSA");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());
            this.DataBase.AddInParameter(dbCommand, "CD_BOLSA", DbType.AnsiString, CodigoBolsa);

            reader = this.DataBase.ExecuteReader(dbCommand);

            while (reader.Read())
            {
                response.Codigo = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["CD_BOLSA"]);
                response.Descricao = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["NM_RESU_BOLSA"]);                
            }

            reader.Close(); //incluido - esertorio - 200804291049 - ref issue ID0003836

            return response;
        }

        public List<BusinessEntity.Bolsa> ConsultarBolsas()
        {
            IDataReader reader;
            List<BusinessEntity.Bolsa> response = new List<Sinacor.Servico.Bovespa.Ordens.BusinessEntity.Bolsa>();
            StringBuilder sql = new StringBuilder();

            sql.AppendLine(" SELECT");
            sql.AppendLine(" NM_RESU_BOLSA,");
            sql.AppendLine(" CD_BOLSA");
            sql.AppendLine(" FROM TGEBOLSA");
            sql.AppendLine(" WHERE CD_SISTEMA = 'BOL'");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());
            reader = this.DataBase.ExecuteReader(dbCommand);

            while (reader.Read())
            { 
                BusinessEntity.Bolsa bolsa = new Sinacor.Servico.Bovespa.Ordens.BusinessEntity.Bolsa();
                bolsa.Codigo = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["CD_BOLSA"]);
                bolsa.Descricao = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["NM_RESU_BOLSA"]);

                response.Add(bolsa);
            }

            reader.Close();

            return response;            
        }

        /// <summary>
        /// Consulta bolsa por codigo
        /// Autor: Eduardo Sertorio
        /// Data: 07/01/2008
        /// </summary>
        /// <param name="codigoBolsa"></param>
        /// <returns>BusinessEntity.Bolsa</returns>
        public BusinessEntity.Bolsa ConsultarPorCodigo(string codigoBolsa)
        {            
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("CD_BOLSA, ");
            sql.AppendLine("NM_RESU_BOLSA ");
            //INTO
            //:Frmcadord.dfsPraca
            //:Frmcadord.dfsNmBolsa
            sql.AppendLine("FROM TGEBOLSA ");
            sql.AppendLine("WHERE CD_BOLSA = :CD_BOLSA ");
            sql.AppendLine("AND CD_SISTEMA = 'BOL' ");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());

            this.DataBase.AddInParameter(dbCommand, "CD_BOLSA", DbType.String, codigoBolsa);

            IDataReader reader = this.DataBase.ExecuteReader(dbCommand);

            BusinessEntity.Bolsa response = null;

            if (reader.Read())
            {
                response = new BusinessEntity.Bolsa();
                response.Codigo = Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader.GetValue(0));
                response.Descricao = Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader.GetValue(1));
            }
            reader.Close();

            return response;
        }

        /// <summary>
        /// Validar Bolsa pelo Usuario
        /// Autor: Eduardo Sertorio
        /// Data: 07/01/2008 
        /// </summary>
        /// <param name="codigoUsuario">int</param>
        /// <returns>string CD_BOLSA</returns>
        public string ConsultarPorUsuario(int codigoUsuario)
        { 
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("b.cd_bolsa ");
            sql.AppendLine("FROM TGEUSUARIO  A,TGELOCAL b, TGEBOLSA c, TGEUSUEMP d ");
            sql.AppendLine("WHERE A.CD_USUARIO = :CD_USUARIO ");
            sql.AppendLine("and d.cd_usuario = a.cd_usuario ");
            sql.AppendLine("and b.cd_empresa = d.cd_empresa ");
            sql.AppendLine("and c.cd_bolsac = b.cd_bolsa ");
            sql.AppendLine("and IN_PRAC_PRINC = 'S' ");
            //into :sPraca
            sql.AppendLine("order by b.cd_bolsa ");

            DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString());

            this.DataBase.AddInParameter(dbCommand, "CD_USUARIO", DbType.Int32, codigoUsuario);

            IDataReader reader = this.DataBase.ExecuteReader(dbCommand);

            string bolsa = null;

            if (reader.Read())
            {
                bolsa = Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader.GetValue(0));
            }
            reader.Close();

            return bolsa;
        }

    }
}
